package org.lsms.dao.impl;

/**
 * implements of the DAO interface
 * @author ZhanXueliang
 * create 2010.8.7
 * last update 2010.8.29
 */

import java.sql.*;
import java.sql.Date;
import java.util.*;

import org.lsms.bean.News;
import org.lsms.bean.Paper;
import org.lsms.bean.Paper_Grade;
import org.lsms.bean.Topic;
import org.lsms.bean.User;
import org.lsms.dao.DAO;
import org.lsms.db.*;

public class DaoImpl implements DAO {

	Connection conn = null;
	public boolean login(User user) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT * FROM lsms_user  WHERE user_login_name=? " +
				"AND user_password=? AND user_permission=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getLoginname());
			stmt.setString(2, user.getPassword());
			stmt.setInt(3, user.getPermission());
			rs = stmt.executeQuery();
			while (rs.next()) {
				flag = true;
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	public boolean signup(User user) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_user(user_login_name,user_password,user_permission,user_title,"+
				      "user_first_name,user_middle_name,user_last_name,user_email," +
				      "user_affiliation,user_country,user_tel,user_fax,user_address," +
				      "user_login_times,user_last_login)" +
				      "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getLoginname());
			stmt.setString(2, user.getPassword());
			stmt.setInt(3,user.getPermission());
			stmt.setString(4, user.getTitle());
			stmt.setString(5, user.getFirstname());
			stmt.setString(6, user.getMiddlename());
			stmt.setString(7, user.getLastname());
			stmt.setString(8, user.getEmail());
			stmt.setString(9, user.getAffiliation());
			stmt.setString(10, user.getCountry());
			stmt.setString(11, user.getTel());
			stmt.setString(12, user.getFax());
			stmt.setString(13, user.getAddress());
			stmt.setInt(14, user.getLogintimes());
			//stmt.setDate(14, new java.sql.Date());
//			Calendar stCal = Calendar.getInstance();
//			java.util.Date utlDate = stCal.getTime();
//			java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime());
//			stmt.setDate(14, sqlDate);
			Calendar stCal = Calendar.getInstance();
			java.sql.Date sqlDate = new java.sql.Date(stCal.getTimeInMillis());
			stmt.setDate(15, sqlDate);
			int res = stmt.executeUpdate();
			if(res==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public int get_user_id(String loginname) {
		int userid = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "select user_id from lsms_user where user_login_name=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, loginname);
			res = stmt.executeQuery();
			while(res.next()){
				userid = res.getInt("user_id");
			}
			stmt.close();
			res.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return userid;
	}
	public boolean update_last_login(User user) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_user SET user_last_login=? where user_login_name=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			Calendar stCal = Calendar.getInstance();
			java.util.Date utlDate = stCal.getTime();
			java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime());
			stmt.setDate(1, sqlDate);
			stmt.setString(2,user.getLoginname());
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean update_login_times(String loginname) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_user SET user_login_times=? " +
				"where user_login_name=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, get_user_login_times(loginname)+1);
			stmt.setString(2, loginname);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	public int get_user_login_times(String loginname) {
		int user_login_times = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT user_login_times FROM lsms_user WHERE user_login_name=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,loginname);
			res = stmt.executeQuery();
			while(res.next()){
				user_login_times = res.getInt("user_login_times");
			}
			stmt.close();
			res.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return user_login_times;
	}
	
	public boolean update_user_info(User user,int userid) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_user SET user_password=?," +
				"user_title=?,user_first_name=?,user_middle_name=?,user_last_name=?," +
				"user_email=?,user_affiliation=?,user_tel=?,user_fax=?," +
				"user_country=?,user_address=? WHERE user_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			//stmt.setString(1, user.getLoginname());
			//System.out.println(user.getLoginname());
			stmt.setString(1, user.getPassword());
			stmt.setString(2, user.getTitle());
			stmt.setString(3, user.getFirstname());
			stmt.setString(4, user.getMiddlename());
			stmt.setString(5, user.getLastname());
			stmt.setString(6, user.getEmail());
			stmt.setString(7, user.getAffiliation());
			stmt.setString(8, user.getTel());
			stmt.setString(9, user.getFax());
			stmt.setString(10, user.getCountry());
			stmt.setString(11, user.getAddress());
			stmt.setInt(12, userid);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean submit_paper(Paper paper) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper (first_topic,second_topic,paper_title," +
				"paper_abstract,paper_keywords,author_id,author_name,paper_status," +
				"paper_submit_date,paper_index_type,contact_author_mail," +
				"contact_author_tel,paper_save_name)" +
				" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, paper.getFirsttopic());
			stmt.setString(2, paper.getSecondtopic());
			stmt.setString(3, paper.getPapertitle());
			stmt.setString(4, paper.getPaperabstract());
			stmt.setString(5, paper.getPaperkeywords());
			stmt.setInt(6, paper.getAuthorid());
			stmt.setString(7, paper.getAuthorname());
			stmt.setInt(8, 0);
			Calendar stCal = Calendar.getInstance();
			java.util.Date utlDate = stCal.getTime();
			java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime());
			stmt.setDate(9, sqlDate);
			stmt.setInt(10, 1);
			stmt.setString(11, paper.getContactauthormail());
			stmt.setString(12, paper.getContactauthortel());
			stmt.setString(13, paper.getSavename());
			int res = stmt.executeUpdate();
			if(res==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap select_all_topic() {
		HashMap<String,String> hm = new HashMap<String,String>();
		String sql = "SELECT topic_name,topic_content FROM lsms_topic";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			for(int i=0;res.next();i++){
				String topicname = res.getString("topic_name");
				String topiccontent = res.getString("topic_content");
				hm.put(topicname, topiccontent);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}

	public HashMap select_all_paper(int userid) {
		//Paper paper = new Paper();
		HashMap<Integer,Paper> hm = new HashMap<Integer,Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,paper_title,author_name," +
				"paper_submit_date,paper_status FROM lsms_paper WHERE " +
				"author_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, userid);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				int id = res.getInt("paper_id");
				//System.out.println(id);
				String papertitle = res.getString("paper_title");
				paper.setPapertitle(papertitle);
				//System.out.println(papertitle);
				String authorname = res.getString("author_name");
				paper.setAuthorname(authorname);
				//System.out.println(authorname);
				Date date = res.getDate("paper_submit_date");
				paper.setPapersubmitdate(date);
				//System.out.println(date);
				int paperstatus = res.getInt("paper_status");
				paper.setPaperstatus(paperstatus);
				//System.out.println(paperstatus);
				hm.put(id, paper);
			}
			res.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return hm;
	}
	
	public HashMap select_paper_assign1(int member_id) {
		HashMap<Integer,Paper> hm = new HashMap<Integer,Paper>();
		String sql = "SELECT paper_id,first_topic,second_topic," +
				"paper_title,author_name FROM lsms_paper WHERE " +
				"paper_id in (SELECT paper_id from lsms_paper_assign_1 " +
				"WHERE pcmember_id=?)";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper  = new Paper();
				Integer i = res.getInt("paper_id");
				paper.setAuthorname(res.getString("author_name"));
				paper.setPapertitle(res.getString("paper_title"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				hm.put(i, paper);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	
	public Date get_paper_assign1_date(int paper_id) {
		conn = DBCon.GetConnectionMysql();
		Date date = null;
		String sql = "SELECT assign_date FROM lsms_paper_assign_1 " +
				"WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				date = res.getDate("assign_date");
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return date;
	}
	
	public HashMap select_passed_paper(int userid) {
		HashMap<Integer,Paper> hm = new HashMap<Integer,Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,paper_title,author_name," +
				"paper_submit_date,paper_status,paper_index_type FROM lsms_paper WHERE " +
				"author_id=? AND paper_status=1";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, userid);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				int id = res.getInt("paper_id");
				//System.out.println(id);
				String papertitle = res.getString("paper_title");
				paper.setPapertitle(papertitle);
				//System.out.println(papertitle);
				String authorname = res.getString("author_name");
				paper.setAuthorname(authorname);
				//System.out.println(authorname);
				Date date = res.getDate("paper_submit_date");
				paper.setPapersubmitdate(date);
				//System.out.println(date);
				int paperstatus = res.getInt("paper_status");
				paper.setPaperstatus(paperstatus);
				//System.out.println(paperstatus);
				int paperindextype = res.getInt("paper_index_type");
				paper.setPaperindextype(paperindextype);
				hm.put(id, paper);
			}
			res.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return hm;
	}
	public String get_paper_sava_name(int paperid) {
		conn = DBCon.GetConnectionMysql();
		String url = "";
		String sql = "SELECT paper_save_name from lsms_paper WHERE paper_id=?";
		ResultSet res = null;
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paperid);
			res = stmt.executeQuery();
			while(res.next()){
				url = res.getString("paper_save_name");
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return url;
	}
	public boolean select_topic(int userid,String topic1,String topic2,String topic3,String topic4) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_user_selected_topic (user_id," +
				"selected_topic_one,selected_topic_two,selected_topic_three" +
				",selected_topic_four) VALUES (?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, userid);
			stmt.setString(2, topic1);
			stmt.setString(3, topic2);
			stmt.setString(4, topic3);
			stmt.setString(5, topic4);
			int res = stmt.executeUpdate();
			if(res==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean check_login_name(String loginname) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT COUNT(user_id) FROM lsms_user WHERE " +
				"user_login_name=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, loginname);
			
			res = stmt.executeQuery();
			int i = -1;
			while(res.next()){
				i = res.getInt("count(user_id)");
			}
			if(i==0){
				flag = true;
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean add_relation_tie(int member_id, int reviewer_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_pcmember_reviewer (pcmember_id," +
				"reviewer_id) VALUES (?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			stmt.setInt(2, reviewer_id);
			int res = stmt.executeUpdate();
			if(res==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap select_reviewer_conf(int pcmember_id) {
		HashMap<Integer,User> hm = new HashMap<Integer,User>();
		String sql = "SELECT user_id,user_login_name,user_email," +
				"user_login_times FROM lsms_user WHERE user_id in (" +
				"SELECT reviewer_id FROM lsms_pcmember_reviewer " +
				"WHERE pcmember_id=?)";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, pcmember_id);
			res = stmt.executeQuery();
			while(res.next()){
				User user = new User();
				int user_id = res.getInt("user_id");
				user.setLoginname(res.getString("user_login_name"));
				user.setEmail(res.getString("user_email"));
				user.setLogintimes(res.getInt("user_login_times"));
				hm.put(user_id, user);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	
	public HashMap select_member_conf() {
		HashMap<Integer,User> hm = new HashMap<Integer,User>();
		String sql = "SELECT user_id,user_login_name,user_email," +
			"user_login_times FROM lsms_user WHERE user_permission=1";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			//stmt.setInt(1, pcmember_id);
			res = stmt.executeQuery();
			while(res.next()){
				User user = new User();
				int user_id = res.getInt("user_id");
				user.setLoginname(res.getString("user_login_name"));
				user.setEmail(res.getString("user_email"));
				user.setLogintimes(res.getInt("user_login_times"));
				hm.put(user_id, user);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	
	
	public HashMap select_accept_member() {
		HashMap<Integer,User> hm = new HashMap<Integer,User>();
		String sql = "SELECT user_id,user_login_name,user_email," +
			"user_login_times FROM lsms_user WHERE user_permission=1 " +
			"AND user_login_times>0";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			//stmt.setInt(1, pcmember_id);
			res = stmt.executeQuery();
			while(res.next()){
				User user = new User();
				int user_id = res.getInt("user_id");
				user.setLoginname(res.getString("user_login_name"));
				user.setEmail(res.getString("user_email"));
				user.setLogintimes(res.getInt("user_login_times"));
				hm.put(user_id, user);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	public HashMap get_site_setting() {
		HashMap<String,Integer> hm = new HashMap<String,Integer>();
		String sql = "SELECT * from lsms_site_setting";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				int paper_submit = res.getInt("paper_submit");
				hm.put("paper_submit", paper_submit);
				int paper_edit = res.getInt("paper_edit");
				hm.put("paper_edit", paper_edit);
				int user_register = res.getInt("user_register");
				hm.put("user_register", user_register);
				int member_invite = res.getInt("member_invite");
				hm.put("member_invite", member_invite);
				int reviewer_invite = res.getInt("reviewer_invite");
				hm.put("reviewer_invite", reviewer_invite);
				int paper_notif_sent = res.getInt("paper_notif_sent");
				hm.put("paper_notif_sent", paper_notif_sent);
				int index_view = res.getInt("index_view");
				hm.put("index_view", index_view);
				int index_upload = res.getInt("index_upload");
				hm.put("index_upload", index_upload);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	
	public boolean upload_site_setting(int[] setting) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_site_setting SET paper_submit=?," +
				"paper_edit=?,user_register=?,member_invite=?," +
				"reviewer_invite=?,index_view=?,paper_notif_sent=?," +
				"index_upload=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			for(int i=1;i<=8;i++){
				stmt.setInt(i, setting[i-1]);
			}
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public String user_select_topics_info(String loginname) {
		String topics = "";
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT selected_topic_one,selected_topic_two," +
				"selected_topic_three,selected_topic_four FROM " +
				"lsms_user_selected_topic WHERE user_id=?";
		int user_id = get_user_id(loginname);
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, user_id);
			res = stmt.executeQuery();
			while(res.next()){
				String topic1 = res.getString("selected_topic_one");
				topics +=topic1;
				topics +=',';
				String topic2 = res.getString("selected_topic_two");
				topics +=topic2;
				topics +=',';
				String topic3 = res.getString("selected_topic_three");
				topics +=topic3;
				topics +=',';
				String topic4 = res.getString("selected_topic_four");
				topics +=topic4;
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return topics;
	}
	
	public int get_paper_assign1_num(String loginname) {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(paper_id) FROM lsms_paper_assign_1 " +
				"WHERE pcmember_id=?";
		int pcmember_id = get_user_id(loginname);
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, pcmember_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt("count(paper_id)");
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public User get_user_info(int user_id) {
		User user = new User();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT * FROM lsms_user WHERE user_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, user_id);
			res = stmt.executeQuery();
			while(res.next()){
				user.setUserid(res.getInt("user_id"));
				user.setAddress(res.getString("user_address"));
				user.setAffiliation(res.getString("user_affiliation"));
				user.setCountry(res.getString("user_country"));
				user.setEmail(res.getString("user_email"));
				user.setFax(res.getString("user_fax"));
				user.setFirstname(res.getString("user_first_name"));
				user.setLastname(res.getString("user_last_name"));
				user.setLoginname(res.getString("user_login_name"));
				user.setLogintimes(res.getInt("user_login_times"));
				user.setMiddlename(res.getString("user_middle_name"));
				user.setPassword(res.getString("user_password"));
				user.setPermission(res.getInt("user_permission"));
				user.setTel(res.getString("user_tel"));
				user.setTitle(res.getString("user_title"));
				user.setLastlogin(res.getDate("user_last_login"));
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return user;
	}
	
	public HashMap get_paper_assign1(int member_id,int page_count, int row) {
		HashMap<String,List<Paper>> hm = new HashMap<String,List<Paper>>();
		List<Paper> list = new ArrayList<Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,first_topic,second_topic," +
		"paper_title,author_name FROM lsms_paper WHERE " +
		"paper_id in (SELECT paper_id from lsms_paper_assign_1 " +
		"WHERE pcmember_id=?) ORDER BY paper_id LIMIT "+(page_count-1)*row +
		","+row;
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper  = new Paper();
				//Integer i = res.getInt("paper_id");
				paper.setPaperid(res.getInt("paper_id"));
				paper.setAuthorname(res.getString("author_name"));
				paper.setPapertitle(res.getString("paper_title"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				list.add(paper);
			}
			res.close();
			stmt.close();
			hm.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return hm;
	}
	
	public int get_unassigned1_num() {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(paper_id) FROM lsms_paper where paper_id " +
				"NOT IN (SELECT paper_id FROM lsms_paper_assign_1)";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public HashMap get_paper_unassign1(int a_page_count, int a_row) {
		HashMap<String,List<Paper>> map = new HashMap<String,List<Paper>>();
		List<Paper> list = new ArrayList<Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,first_topic,second_topic,paper_title," +
				"author_name FROM lsms_paper WHERE paper_id NOT IN " +
				"(SELECT paper_id FROM lsms_paper_assign_1) LIMIT " +
				(a_page_count-1)*a_row + "," + a_row;
		PreparedStatement stmt= null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				paper.setPaperid(res.getInt("paper_id"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				paper.setAuthorname(res.getString("author_name"));
				paper.setPapertitle(res.getString("paper_title"));
				list.add(paper);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean paper_assign1(int paper_id, int member_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper_assign_1 (paper_id,pcmember_id," +
				"assign_status,assign_date) VALUES (?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, member_id);
			stmt.setInt(3, 0);
			Calendar stCal = Calendar.getInstance();
			java.util.Date utlDate = stCal.getTime();
			java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime());
			stmt.setDate(4, sqlDate);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public int get_paper_assign_reviewer_num(int member_id, int paper_id) {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(reviewer_id) FROM lsms_paper_assign_2 " +
				"WHERE pcmember_id=? AND paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			stmt.setInt(2, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public Paper get_paper_info(int paper_id) {
		Paper paper = new Paper();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT * FROM lsms_paper WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				paper.setAuthorid(res.getInt("author_id"));
				paper.setAuthorname(res.getString("author_name"));
				paper.setContactauthormail(res.getString("contact_author_mail"));
				paper.setContactauthortel(res.getString("contact_author_tel"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				paper.setPaperabstract(res.getString("paper_abstract"));
				paper.setPaperid(res.getInt("paper_id"));
				paper.setPaperindextype(res.getInt("paper_index_type"));
				paper.setPaperkeywords(res.getString("paper_keywords"));
				paper.setPaperstatus(res.getInt("paper_status"));
				paper.setPapersubmitdate(res.getDate("paper_submit_date"));
				paper.setPapertitle(res.getString("paper_title"));
				paper.setSavename(res.getString("paper_save_name"));
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return paper;
	}
	
	public String get_topic_content(String topicname) {
		String content = "";
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT topic_content FROM lsms_topic " +
				"WHERE topic_name=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, topicname);
			res = stmt.executeQuery();
			while(res.next()){
				content = res.getString(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return content;
	}
	
	public HashMap get_paper_assign2(int paper_id, int member_id,
			int page_count, int row) {
		HashMap<String,List<User>> map = new HashMap<String,List<User>>();
		List<User> list = new ArrayList<User>();
		String sql = "SELECT user_id,user_login_name,user_email FROM " +
				"lsms_user WHERE user_id IN (SELECT reviewer_id FROM " +
				"lsms_paper_assign_2 WHERE paper_id=? AND pcmember_id=?) " +
				"LIMIT "+(page_count-1)*row + "," + row;
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				User user = new User();
				user.setUserid(res.getInt("user_id"));
				user.setLoginname(res.getString("user_login_name"));
				user.setEmail(res.getString("user_email"));
				list.add(user);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public int get_reviewer_num_of_one_paper(int paper_id, int member_id) {
		int num = 0;
		String sql = "SELECT count(reviewer_id) FROM lsms_paper_assign_2 " +
				"WHERE paper_id=? AND pcmember_id=?";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public int get_paper_assign2_status(int paper_id, int member_id,
			int reviewer_id) {
		int status = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT assign_status FROM lsms_paper_assign_2 " +
				"WHERE paper_id=? AND pcmember_id=? AND reviewer_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, member_id);
			stmt.setInt(3, reviewer_id);
			res = stmt.executeQuery();
			while(res.next()){
				status =  res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return status;
	}
	
	public int get_unassign2_num(int paper_id) {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(user_id) FROM lsms_user WHERE " +
				"user_id NOT IN (SELECT reviewer_id FROM lsms_paper_assign_2 " +
				"WHERE paper_id=?) AND user_permission=2 AND user_login_times>0";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public HashMap get_reviewer_unassign2(int paper_id,int a_page_count,int a_row) {
		HashMap<String,List<User>> map = new HashMap<String,List<User>>();
		List<User> list = new ArrayList<User>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT user_id,user_login_name,user_email FROM " +
				"lsms_user WHERE user_id NOT IN (SELECT reviewer_id " +
				"FROM lsms_paper_assign_2 WHERE paper_id=?) AND user_permission" +
				"=2 AND user_login_times>0 LIMIT " + (a_page_count-1)*a_row + "," + a_row;
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				User user = new User();
				user.setUserid(res.getInt("user_id"));
				user.setLoginname(res.getString("user_login_name"));
				user.setEmail(res.getString("user_email"));
				list.add(user);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public int get_reviewer_assign_num(int reviewer_id) {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(paper_assign_id) FROM lsms_paper_assign_2 " +
				"WHERE reviewer_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, reviewer_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public boolean paper_assign2(int member_id, int reviewer_id, int paper_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper_assign_2 (pcmember_id,reviewer_id," +
				"paper_id,assign_status,assign_date) VALUES (?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			stmt.setInt(2, reviewer_id);
			stmt.setInt(3, paper_id);
			stmt.setInt(4, 0);
			Calendar stCal = Calendar.getInstance();
			java.util.Date utlDate = stCal.getTime();
			java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime());
			stmt.setDate(5, sqlDate);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_paper_assigned_to_reviewer(int reviewer_id) {
		HashMap<Integer,Paper> map = new HashMap<Integer,Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT DISTINCT pcmember_id,lsms_paper.paper_id,author_name,paper_title FROM " +
				"lsms_paper,lsms_paper_assign_2 WHERE lsms_paper.paper_id IN (SELECT paper_id " +
				"FROM lsms_paper_assign_2 WHERE reviewer_id=?) AND lsms_paper.paper_id=" +
				"lsms_paper_assign_2.paper_id";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, reviewer_id);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				Integer member_id = res.getInt("pcmember_id");
				Integer paper_id = res.getInt("lsms_paper.paper_id");
				paper.setPaperid(member_id);
				paper.setAuthorname(res.getString("author_name"));
				paper.setPapertitle(res.getString("paper_title"));
				map.put(paper_id, paper);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean paper_grade(Paper_Grade paper_grade, int reviewer_id,
			int paper_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper_grade (paper_id,reviewer_id," +
				"problem_a,problem_b,problem_c,problem_d,problem_e," +
				"reviewer_expertise,reviewer_to_author,reviewer_to_member," +
				"reviewer_recommend) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, reviewer_id);
			stmt.setInt(3, paper_grade.getProblem_a());
			stmt.setInt(4, paper_grade.getProblem_b());
			stmt.setInt(5, paper_grade.getProblem_c());
			stmt.setInt(6, paper_grade.getProblem_d());
			stmt.setInt(7, paper_grade.getProblem_e());
			stmt.setString(8, paper_grade.getExpertise());
			stmt.setString(9, paper_grade.getNote_to_author());
			stmt.setString(10, paper_grade.getNote_to_member());
			stmt.setInt(11, paper_grade.getRecommend());
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean update_paper_assign2_status(int status,int paper_id, int reviewer_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql =  "UPDATE lsms_paper_assign_2 SET assign_status=? WHERE " +
				"paper_id=? AND reviewer_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, status);
			stmt.setInt(2, paper_id);
			stmt.setInt(3, reviewer_id);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_member_undecision_paper(int member_id, int page_count,
			int row) {
		HashMap<String,List<Paper>> map = new HashMap<String,List<Paper>>();
		List<Paper> list = new ArrayList<Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,first_topic,second_topic," +
				"paper_title,author_name FROM lsms_paper WHERE " +
				"paper_id IN (SELECT paper_id FROM lsms_paper_assign_1 " +
				"WHERE pcmember_id=? AND assign_status=0) LIMIT "+
				(page_count-1)*row + "," +row;
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				paper.setPaperid(res.getInt("paper_id"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				paper.setAuthorname(res.getString("author_name"));
				paper.setPapertitle(res.getString("paper_title"));
				list.add(paper);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public int get_member_undecision_paper_num(int member_id) {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(paper_id) FROM lsms_paper_assign_1 " +
				"WHERE assign_status=0 AND pcmember_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, member_id);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public HashMap get_paper_grade(int paper_id) {
		HashMap<String,List<Paper_Grade>> map = new HashMap<String,List<Paper_Grade>>();
		List<Paper_Grade> list = new ArrayList<Paper_Grade>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT * FROM lsms_paper_grade WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				Paper_Grade paper_grade = new Paper_Grade();
				paper_grade.setReviewer_id(res.getInt("reviewer_id"));
				paper_grade.setExpertise(res.getString("reviewer_expertise"));
				paper_grade.setNote_to_author(res.getString("reviewer_to_author"));
				paper_grade.setNote_to_member(res.getString("reviewer_to_member"));
				paper_grade.setProblem_a(res.getInt("problem_a"));
				paper_grade.setProblem_b(res.getInt("problem_b"));
				paper_grade.setProblem_c(res.getInt("problem_c"));
				paper_grade.setProblem_d(res.getInt("problem_d"));
				paper_grade.setProblem_e(res.getInt("problem_e"));
				paper_grade.setRecommend(res.getInt("reviewer_recommend"));
				list.add(paper_grade);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean member_paper_comment(int paper_id,String member_to_chair,int recommend) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper_comment (paper_id," +
				"member_to_chair,pcmember_recommend) VALUES (?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setString(2, member_to_chair);
			stmt.setInt(3, recommend);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean update_paper_assign_1_status(int status,int paper_id, int member_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_paper_assign_1 SET assign_status=? " +
				"WHERE paper_id=? AND pcmember_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, status);
			stmt.setInt(2, paper_id);
			stmt.setInt(3, member_id);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_all_paper(int page_count, int row) {
		HashMap<String,List<Paper>> map = new HashMap<String,List<Paper>>();
		List<Paper> list = new ArrayList<Paper>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT paper_id,paper_title,first_topic,second_topic," +
				"author_name,paper_submit_date,paper_status FROM lsms_paper " +
				"LIMIT "+(page_count-1)*row+","+row;
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				Paper paper = new Paper();
				paper.setPaperid(res.getInt("paper_id"));
				paper.setFirsttopic(res.getString("first_topic"));
				paper.setSecondtopic(res.getString("second_topic"));
				paper.setPapertitle(res.getString("paper_title"));
				paper.setPapersubmitdate(res.getDate("paper_submit_date"));
				paper.setPaperstatus(res.getInt("paper_status"));
				paper.setAuthorname(res.getString("author_name"));
				list.add(paper);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public int get_num_of_all_paper() {
		int num = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT count(paper_id) FROM lsms_paper";
		ResultSet res = null;
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				num = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return num;
	}
	
	public boolean update_paper_status(int status, int paper_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_paper SET paper_status=? WHERE " +
				"paper_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, status);
			stmt.setInt(2, paper_id);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_member_comment(int paper_id) {
		HashMap<Integer,String> map = new HashMap<Integer,String>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT member_to_chair,pcmember_recommend FROM " +
				"lsms_paper_comment WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				int recommend = res.getInt("pcmember_recommend");
				String note = res.getString("member_to_chair");
				map.put(recommend, note);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean reset_pass(String loginname, String email) {
		boolean flag = false;
		String sql = "UPDATE lsms_user SET user_password=? WHERE " +
				"user_login_name=? AND user_email=?";
		conn = DBCon.GetConnectionMysql();
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "123456");
			stmt.setString(2, loginname);
			stmt.setString(3, email);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_all_topic() {
		HashMap<String,List<Topic>> map = new HashMap<String,List<Topic>>();
		List<Topic> list = new ArrayList<Topic>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT topic_name,topic_content FROM lsms_topic " +
				"ORDER BY topic_class,topic_id";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				Topic topic = new Topic();
				topic.setTopicname(res.getString("topic_name"));
				topic.setTopiccontent(res.getString("topic_content"));
				list.add(topic);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public String get_mail_content(int content_id) {
		conn = DBCon.GetConnectionMysql();
		String content = "";
		String sql = "SELECT mail_content FROM lsms_mail_content WHERE " +
				"mail_content_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, content_id);
			res = stmt.executeQuery();
			while(res.next()){
				content +=res.getString("mail_content");
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return content;
	}
	
	public int get_max_paper_id() {
		int max_id = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT MAX(paper_id) FROM lsms_paper";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				max_id = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return max_id;
	}
	
	public HashMap get_paper_contact_mail(int type,int start_id) {
		HashMap<Integer,String> map = new HashMap<Integer,String>();
		conn = DBCon.GetConnectionMysql();
		String sql1 = "SELECT paper_id,contact_author_mail FROM lsms_paper " +
				"WHERE paper_status=? AND paper_id >= ?";
		String sql2 = "SELECT paper_id,contact_author_mail FROM lsms_paper " +
				"WHERE paper_id >= ?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			if(type==0){
				stmt = conn.prepareStatement(sql2);
				stmt.setInt(1, start_id);
				res = stmt.executeQuery();
				while(res.next()){
					Integer paper_id = res.getInt("paper_id");
					String contact_author_mail = res.getString("contact_author_mail");
					map.put(paper_id, contact_author_mail);
				}
			}else{
				stmt = conn.prepareStatement(sql1);
				stmt.setInt(1, type);
				stmt.setInt(2, start_id);
				res = stmt.executeQuery();
				while(res.next()){
					Integer paper_id = res.getInt("paper_id");
					String contact_author_mail = res.getString("contact_author_mail");
					map.put(paper_id, contact_author_mail);
				}
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public News get_news_info(int news_id) {
		News news = new News();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT * FROM lsms_news WHERE news_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, news_id);
			res = stmt.executeQuery();
			while(res.next()){
				news.setNews_content(res.getString("news_content"));
				news.setNews_is_top(res.getInt("news_is_top"));
				news.setNews_release_date(res.getDate("news_release_date"));
				news.setNews_title(res.getString("news_title"));
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return news;
	}
	
	public int get_max_id_news() {
		int max_id = 0;
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT MAX(news_id) FROM lsms_news";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			while(res.next()){
				max_id = res.getInt(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return max_id;
	}
	
	public boolean materail_upload(int paper_id, int type, String savename) {
		conn = DBCon.GetConnectionMysql();
		boolean flag = false;
		String sql = "UPDATE lsms_paper SET ";
		switch(type){
		case 1:
			sql +="paper_save_name ";
			break;
		case 2:
			sql +="paper_upload_1 ";
			break;
		case 3:
			sql +="paper_upload_2 ";
			break;
		case 4:
			sql +="paper_upload_3 ";
			break;
		}
		sql +="=? WHERE paper_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, savename);
			stmt.setInt(2, paper_id);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap get_all_news() {
		HashMap<String,List<News>> map = new HashMap<String,List<News>>();
		List<News> list = new ArrayList<News>();
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT news_id,news_title,news_release_date,news_is_top FROM lsms_news";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			res =  stmt.executeQuery();
			while(res.next()){
				News news = new News();
				news.setNews_id(res.getInt("news_id"));
				news.setNews_release_date(res.getDate("news_release_date"));
				news.setNews_title(res.getString("news_title"));
				news.setNews_is_top(res.getInt("news_is_top"));
				list.add(news);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean release_new_note(News news) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_news (news_title," +
				"news_content,news_release_date,news_is_top) " +
				"VALUES (?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, news.getNews_title());
			stmt.setString(2, news.getNews_content());
			Calendar cal = Calendar.getInstance();
			java.util.Date date = cal.getTime();
			java.sql.Date date2 = new java.sql.Date(date.getTime());
			stmt.setDate(3, date2);
			stmt.setInt(4, news.getNews_is_top());
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean modify_mail_content(String content, int content_id) {
		boolean flag = false;
		conn = DBCon.GetConnectionMysql();
		String sql = "UPDATE lsms_mail_content SET mail_content=? WHERE " +
				"mail_content_id=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, content);
			stmt.setInt(2, content_id);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public HashMap member_see_note(int paper_id,int reviewer_id) {
		conn = DBCon.GetConnectionMysql();
		HashMap<String,String> map = new HashMap<String,String>();
		String sql = "SELECT reviewer_to_member,reviewer_to_author FROM " +
				"lsms_paper_grade WHERE paper_id=? AND reviewer_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setInt(2, reviewer_id);
			res = stmt.executeQuery();
			while(res.next()){
				String to_member = res.getString("reviewer_to_member");
				String to_author = res.getString("reviewer_to_author");
				map.put("to_member", to_member);
				map.put("to_author", to_author);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	public boolean chair_comment(int paper_id,String comment) {
		conn = DBCon.GetConnectionMysql();
		String sql = "INSERT INTO lsms_paper_comment_2 (paper_id,chair_to_author) " +
				"VALUES (?,?)";
		boolean flag = false;
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			stmt.setString(2, comment);
			int row = stmt.executeUpdate();
			if(row==1){
				flag = true;
			}
			stmt.close();			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
	}
	
	public String get_chair_suggestion(int paper_id) {
		String suggestion = "";
		conn = DBCon.GetConnectionMysql();
		String sql = "SELECT chair_to_author FROM lsms_paper_comment_2 " +
				"WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				suggestion = res.getString(1);
			}
			res.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return suggestion;
	}
	
	public HashMap get_reviewer_suggrstion(int paper_id) {
		conn = DBCon.GetConnectionMysql();
		HashMap<String,List<String>> map = new HashMap<String,List<String>>();
		List<String> list = new ArrayList<String>();
		String sql = "SELECT reviewer_to_author FROM lsms_paper_grade " +
				"WHERE paper_id=?";
		PreparedStatement stmt = null;
		ResultSet res = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, paper_id);
			res = stmt.executeQuery();
			while(res.next()){
				String sugg = res.getString(1);
				list.add(sugg);
			}
			res.close();
			stmt.close();
			map.put("all", list);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	
	
}
